<?php

namespace Icsoc\ReportBundle\Command;

use Symfony\Bundle\FrameworkBundle\Command\ContainerAwareCommand;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;
use Guzzle\Http\Client;

/**
 * Class DealIncdrCommand
 * @package Icsoc\ReportBundle\Command
 */
class DealIncdrCommand extends ContainerAwareCommand
{
    const SHELL_COMMAND = 'ps aux|grep "report:deal:incdr"';

    /**
     * Configures the current command.
     */
    protected function configure()
    {
        $this
            ->setName('report:deal:incdr')
            ->setDescription('处理呼入明细呼入地区表、按小时进线统计表、手机固话报表数据定时执行服务');
    }

    /**
     * Executes the current command.
     *
     * This method is not abstract because you can use this class
     * as a concrete class. In this case, instead of defining the
     * execute() method, you set the code to execute by passing
     * a Closure to the setCode() method.
     *
     * @param InputInterface $input An InputInterface instance
     * @param OutputInterface $output An OutputInterface instance
     *
     * @return null|int null or 0 if everything went fine, or an error code
     *
     * @throws \LogicException When this abstract method is not implemented
     *
     * @see setCode()
     */
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        //检查报表服务进程是否在运行
        @exec(self::SHELL_COMMAND, $result);
        $count = isset($result[0]) ? count($result) : 0;
        if ($count > 4) {
            //进程正在执行
            $output->writeln(sprintf(
                '[%s] 报表服务INCDR进程正在执行，无法执行该命令，结果为【%s】.',
                date('Y-m-d H:i:s'),
                var_export($result, true)
            ));

            return 1;
        }

        $logger = $this->getContainer()->get('incdr_logger');
        $timestart = microtime(true);
        $this->dealIncdr();
        $timeend = microtime(true);
        $logger->info("执行了 ".($timeend - $timestart)." 秒\r\n");

        return 0;
    }

    /**
     * 处理呼入明细数据
     */
    protected function dealIncdr()
    {
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->getContainer()->get('doctrine.dbal.default_connection');

        $result = $conn->fetchAll(
            "SELECT id,vcc_id,que_id,que_name,group_id,server_num,caller,caller_areacode,caller_areaname,caller_type,
            start_year,start_month,start_day,result,start_hour
            FROM win_incdr
            WHERE if_done = 0 LIMIT 5000"
        );

        /** @var  $province (获取所有省份) */
        $province = $this->getProvince();

        foreach ($result as $row) {
            /** @var  $caller (处理主叫号码)*/
            //$caller = trim($row['caller']);
            /** @var  $areainfo (号码信息) */
            /*$areainfo = $this->getCallerInfo($caller);
            if ($areainfo == false) {
                continue;
            }*/
            /** @var  $province_name (主叫所在地区)*/
            $areainfo['code'] = $row['caller_areacode'];
            $areainfo['city'] = $row['caller_areaname'];
            $areainfo['type'] = $row['caller_type'];
            $areainfo['province_name'] = isset($province[$areainfo['code']]) ? $province[$areainfo['code']] : '其他';

            /** 更新呼入明细数据 */
            $conn->query(
                "UPDATE win_incdr SET if_done = 1 WHERE id ='".$row['id']."'"
            );

            /** 更新呼入地区统计报表 */
            $startDate = $row['start_year'].'-'.$row['start_month'].'-'.$row['start_day'];
            $updateValue = array('nums'=>1);
            $fieldValue = array(
                'vcc_id'=>$row['vcc_id'],
                'server_num'=>$row['server_num'],
                'que_id'=>$row['que_id'],
                'que_name'=>$row['que_name'],
                'group_id'=>$row['group_id'],
                'area_code'=>$areainfo['code'],
                'area_name'=>$areainfo['city'],
                'province_name'=>$areainfo['province_name'],
                'start_year'=>$row['start_year'],
                'start_month'=>$row['start_month'],
                'start_day'=>$row['start_day'],
                'start_date'=>$startDate,
                'nums'=>1,
            );
            $this->insertOnDuplicateKeyUpdate('rep_incdr_area', $fieldValue, $updateValue);

            /** 更新按小时进线统计报表 */
            $updateValue = array('nums'=>1);
            $fieldValue = array(
                'vcc_id'=>$row['vcc_id'],
                'server_num'=>$row['server_num'],
                'que_id'=>$row['que_id'],
                'que_name'=>$row['que_name'],
                'group_id'=>$row['group_id'],
                'start_hour'=>$row['start_hour'],
                'start_year'=>$row['start_year'],
                'start_month'=>$row['start_month'],
                'start_day'=>$row['start_day'],
                'start_date'=>$startDate,
                'nums'=>1,
            );
            $this->insertOnDuplicateKeyUpdate('rep_hour_innums', $fieldValue, $updateValue);

            /** 手机固话统计报表 */
            $updateValue = array('nums'=>1);
            $fieldValue = array(
                'vcc_id'=>$row['vcc_id'],
                'caller_type'=>$areainfo['type'] == 'MOBILE' ? 0 : 1,
                'start_year'=>$row['start_year'],
                'start_month'=>$row['start_month'],
                'start_day'=>$row['start_day'],
                'start_date'=>$startDate,
                'nums'=>1,
            );
            $this->insertOnDuplicateKeyUpdate('rep_caller_types', $fieldValue, $updateValue);
        }
    }

    /**
     * 或如号码信息
     *
     * @param string $phone
     * @return array
     */
    protected function getCallerInfo($phone = '')
    {
        $logger = $this->getContainer()->get('incdr_logger');

        $easycti = $this->getContainer()->getParameter('easycti');
        $localCode = $this->getContainer()->getParameter('local_code');
        $localCity = $this->getContainer()->getParameter('local_city');

        $client   = new Client();
        $request = $client->get($easycti.'/attribution?phone='.$phone);
        $response = $request->send()->json();

        if (json_last_error() !== JSON_ERROR_NONE) {
            $logger->info('解析结果出错，错误为【'.json_last_error().'】');
        }

        $code    = isset($response['code']) ? $response['code'] : 0;
        $message = isset($response['message']) ? $response['message'] : 0;
        $data    = isset($response['data']) ? $response['data'] : array();

        $info = array(
            'city' => '',
            'code' => '',
            'type' => '-1',
        );

        if ($code != 200) {
            $logger->info($message.$phone);

            return false;
        } else {
            $info['code'] = !empty($data['code']) ? $data['code'] : $localCode;
            $info['city'] = !empty($data['city']) ? $data['city'] : $localCity;
            $info['type'] = (isset($data['type']) && $data['type'] == 'MOBILE') ? 0 : 1;
        }

        return $info;
    }

    /**
     * 使用ON DUPLICATE KEY判断是插入还是更新
     *
     * @param $tableName
     * @param array $insertValue
     * @param array $updateValue
     *
     * @return bool
     */
    protected function insertOnDuplicateKeyUpdate($tableName, array $insertValue, array $updateValue = array())
    {
        if (empty($tableName)) {
            return false;
        }

        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->getContainer()->get('doctrine.dbal.default_connection');

        $values = array();
        $sets = array();
        foreach ($insertValue as $key => $value) {
            $values[$key] = "'".$value."'";
        }

        foreach ($updateValue as $key => $value) {
            if (array_key_exists($key, $insertValue) == true) {
                if (is_int($value) || is_float($value)) {
                    $sets[] = $key.'= '.$key.' + '.$value;
                } else {
                    $sets[] = $key." = '".$value."'";
                }
            }
        }

        if (empty($values) || empty($sets)) {
            return false;
        }

        $conn->query(
            'INSERT INTO ' . $tableName . ' (' . implode(', ', array_keys($insertValue)) . ')' .
            ' VALUES (' . implode(', ', $values) . ')' .
            ' ON DUPLICATE KEY UPDATE ' . implode(', ', $sets)
        );

        return true;
    }

    /**
     * 获取区号省份对应信息
     *
     * @return array
     */
    protected function getProvince()
    {
        $province = array(
            '010' => '北京',
            '020' => '广东',
            '0750' => '广东',
            '0751' => '广东',
            '0752' => '广东',
            '0753' => '广东',
            '0754' => '广东',
            '0755' => '广东',
            '0756' => '广东',
            '0757' => '广东',
            '0758' => '广东',
            '0759' => '广东',
            '0760' => '广东',
            '0762' => '广东',
            '0763' => '广东',
            '0766' => '广东',
            '0768' => '广东',
            '0769' => '广东',
            '0660' => '广东',
            '0662' => '广东',
            '0663' => '广东',
            '0668' => '广东',
            '021' => '上海',
            '022' => '天津',
            '023' => '重庆',
            '8489' => '重庆',
            '8299' => '广东',
            '0410' => '辽宁',
            '0411' => '辽宁',
            '0412' => '辽宁',
            '0413' => '辽宁',
            '0414' => '辽宁',
            '0415' => '辽宁',
            '0416' => '辽宁',
            '0417' => '辽宁',
            '0418' => '辽宁',
            '0419' => '辽宁',
            '0421' => '辽宁',
            '0427' => '辽宁',
            '0429' => '辽宁',
            '024' => '辽宁',
            '0510' => '江苏',
            '0511' => '江苏',
            '0512' => '江苏',
            '0513' => '江苏',
            '0514' => '江苏',
            '0515' => '江苏',
            '0516' => '江苏',
            '0517' => '江苏',
            '0518' => '江苏',
            '0519' => '江苏',
            '0523' => '江苏',
            '0527' => '江苏',
            '025' => '江苏',
            '0710' => '湖北',
            '0711' => '湖北',
            '0712' => '湖北',
            '0713' => '湖北',
            '0714' => '湖北',
            '0715' => '湖北',
            '0716' => '湖北',
            '0717' => '湖北',
            '0718' => '湖北',
            '0719' => '湖北',
            '0722' => '湖北',
            '0724' => '湖北',
            '0728' => '湖北',
            '027' => '湖北',
            '028' => '四川',
            '0812' => '四川',
            '0813' => '四川',
            '0816' => '四川',
            '0817' => '四川',
            '0818' => '四川',
            '0825' => '四川',
            '0826' => '四川',
            '0827' => '四川',
            '0830' => '四川',
            '0831' => '四川',
            '0832' => '四川',
            '0833' => '四川',
            '0834' => '四川',
            '0835' => '四川',
            '0836' => '四川',
            '0837' => '四川',
            '0838' => '四川',
            '0839' => '四川',
            '0910' => '陕西',
            '0911' => '陕西',
            '0912' => '陕西',
            '0913' => '陕西',
            '0914' => '陕西',
            '0915' => '陕西',
            '0916' => '陕西',
            '0917' => '陕西',
            '0919' => '陕西',
            '029' => '陕西',
            '0310' => '河北',
            '0311' => '河北',
            '0312' => '河北',
            '0313' => '河北',
            '0314' => '河北',
            '0315' => '河北',
            '0316' => '河北',
            '0317' => '河北',
            '0318' => '河北',
            '0319' => '河北',
            '0335' => '河北',
            '0349' => '山西',
            '0350' => '山西',
            '0351' => '山西',
            '0352' => '山西',
            '0353' => '山西',
            '0356' => '山西',
            '0357' => '山西',
            '0358' => '山西',
            '0359' => '山西',
            '0370' => '河南',
            '0371' => '河南',
            '0372' => '河南',
            '0373' => '河南',
            '0374' => '河南',
            '0375' => '河南',
            '0377' => '河南',
            '0378' => '河南',
            '0379' => '河南',
            '0391' => '河南',
            '0392' => '河南',
            '0393' => '河南',
            '0394' => '河南',
            '0395' => '河南',
            '0396' => '河南',
            '0398' => '河南',
            '0431' => '吉林',
            '0432' => '吉林',
            '0433' => '吉林',
            '0434' => '吉林',
            '0435' => '吉林',
            '0436' => '吉林',
            '0437' => '吉林',
            '0438' => '吉林',
            '0439' => '吉林',
            '0451' => '黑龙江',
            '0452' => '黑龙江',
            '0453' => '黑龙江',
            '0454' => '黑龙江',
            '0455' => '黑龙江',
            '0456' => '黑龙江',
            '0457' => '黑龙江',
            '0458' => '黑龙江',
            '0459' => '黑龙江',
            '0464' => '黑龙江',
            '0467' => '黑龙江',
            '0468' => '黑龙江',
            '0469' => '黑龙江',
            '0470' => '内蒙古',
            '0471' => '内蒙古',
            '0472' => '内蒙古',
            '0473' => '内蒙古',
            '0474' => '内蒙古',
            '0475' => '内蒙古',
            '0476' => '内蒙古',
            '0477' => '内蒙古',
            '0478' => '内蒙古',
            '0479' => '内蒙古',
            '0482' => '内蒙古',
            '0483' => '内蒙古',
            '0530' => '山东',
            '0531' => '山东',
            '0532' => '山东',
            '0533' => '山东',
            '0534' => '山东',
            '0535' => '山东',
            '0536' => '山东',
            '0537' => '山东',
            '0538' => '山东',
            '0539' => '山东',
            '0543' => '山东',
            '0546' => '山东',
            '0631' => '山东',
            '0632' => '山东',
            '0633' => '山东',
            '0634' => '山东',
            '0635' => '山东',
            '0550' => '安徽',
            '0551' => '安徽',
            '0552' => '安徽',
            '0553' => '安徽',
            '0554' => '安徽',
            '0555' => '安徽',
            '0556' => '安徽',
            '0557' => '安徽',
            '0558' => '安徽',
            '0559' => '安徽',
            '0561' => '安徽',
            '0562' => '安徽',
            '0563' => '安徽',
            '0564' => '安徽',
            '0565' => '安徽',
            '0566' => '安徽',
            '0570' => '浙江',
            '0571' => '浙江',
            '0572' => '浙江',
            '0573' => '浙江',
            '0574' => '浙江',
            '0575' => '浙江',
            '0576' => '浙江',
            '0577' => '浙江',
            '0578' => '浙江',
            '0579' => '浙江',
            '0580' => '浙江',
            '0591' => '福建',
            '0592' => '福建',
            '0593' => '福建',
            '0594' => '福建',
            '0595' => '福建',
            '0596' => '福建',
            '0597' => '福建',
            '0598' => '福建',
            '0599' => '福建',
            '0691' => '云南',
            '0692' => '云南',
            '0870' => '云南',
            '0871' => '云南',
            '0872' => '云南',
            '0873' => '云南',
            '0874' => '云南',
            '0875' => '云南',
            '0876' => '云南',
            '0877' => '云南',
            '0878' => '云南',
            '0879' => '云南',
            '0883' => '云南',
            '0886' => '云南',
            '0887' => '云南',
            '0888' => '云南',
            '0701' => '江西',
            '0790' => '江西',
            '0791' => '江西',
            '0792' => '江西',
            '0793' => '江西',
            '0794' => '江西',
            '0795' => '江西',
            '0796' => '江西',
            '0797' => '江西',
            '0798' => '江西',
            '0799' => '江西',
            '0730' => '湖南',
            '0731' => '湖南',
            '0732' => '湖南',
            '0733' => '湖南',
            '0734' => '湖南',
            '0735' => '湖南',
            '0736' => '湖南',
            '0737' => '湖南',
            '0738' => '湖南',
            '0739' => '湖南',
            '0743' => '湖南',
            '0744' => '湖南',
            '0745' => '湖南',
            '0746' => '湖南',
            '0770' => '广西',
            '0771' => '广西',
            '0772' => '广西',
            '0773' => '广西',
            '0774' => '广西',
            '0775' => '广西',
            '0776' => '广西',
            '0777' => '广西',
            '0778' => '广西',
            '0779' => '广西',
            '0851' => '贵州',
            '0852' => '贵州',
            '0853' => '贵州',
            '0854' => '贵州',
            '0855' => '贵州',
            '0856' => '贵州',
            '0857' => '贵州',
            '0858' => '贵州',
            '0859' => '贵州',
            '0891' => '西藏',
            '0892' => '西藏',
            '0893' => '西藏',
            '0894' => '西藏',
            '0895' => '西藏',
            '0896' => '西藏',
            '0897' => '西藏',
            '0898' => '海南',
            '0899' => '海南',
            '0890' => '海南',
            '0990' => '新疆',
            '0991' => '新疆',
            '0992' => '新疆',
            '0993' => '新疆',
            '0994' => '新疆',
            '0995' => '新疆',
            '0996' => '新疆',
            '0997' => '新疆',
            '0998' => '新疆',
            '0999' => '新疆',
            '0901' => '新疆',
            '0902' => '新疆',
            '0903' => '新疆',
            '0908' => '新疆',
            '0909' => '新疆',
            '0930' => '甘肃',
            '0931' => '甘肃',
            '0932' => '甘肃',
            '0933' => '甘肃',
            '0934' => '甘肃',
            '0935' => '甘肃',
            '0936' => '甘肃',
            '0937' => '甘肃',
            '0938' => '甘肃',
            '0939' => '甘肃',
            '0941' => '甘肃',
            '0943' => '甘肃',
            '0951' => '宁夏',
            '0952' => '宁夏',
            '0953' => '宁夏',
            '0954' => '宁夏',
            '0955' => '宁夏',
            '0970' => '青海',
            '0971' => '青海',
            '0972' => '青海',
            '0973' => '青海',
            '0974' => '青海',
            '0975' => '青海',
            '0976' => '青海',
            '0977' => '青海',
            '0978' => '青海',
            '0979' => '青海',
            '852' => '香港',
            '853' => '澳门',
            '886' => '台湾',
        );

        return $province;
    }
}
